package Database;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.LinkedList;

import Beans.Veiling;
import DAOs.VeilingDAO;


/**
 * 
 * @author s92314
 *
 */
public class DBmanager {
	
	private static DBmanager uniqueInstance=null;
	
	private Connection con = null ;
	private final String driver = "org.apache.derby.jdbc.EmbeddedDriver" ;
	private static String dbname = "jdbc:derby:derbyDB";
	  
	
	private DBmanager(){
		if(!dbExists(dbname))
		{
			createDatabase();				
		}	
	}

	public static synchronized DBmanager getInstance() {
		if (uniqueInstance==null) {
			uniqueInstance = new DBmanager();
		}
		return uniqueInstance;
	}
	
	private boolean dbExists(String dataSource) {
		boolean exists = false ;
		try{
			Class.forName(driver) ;
            con = DriverManager.getConnection(dataSource);
           
			exists = true ;
		} catch(Exception e){
			; // Do nothing, as DB does not (yet) exist
		}
		return(exists) ;
	}

	public void close() {
		try {
			con = DriverManager.getConnection(dbname + ";shutdown=true") ;
		}catch(SQLException se) {
			; // Do Nothing. System has shut down.
		con = null ;
		}
	}
	
	public Connection getConnection() {
		return con;
	}

	public LinkedList<Veiling> getAllVeilingen() {
		VeilingDAO vd = new VeilingDAO();
		
		return  vd.getAllVeilingen();
	}

	public static void setLocation(String databaseLocation) {
		dbname = databaseLocation;
		System.out.println("DB locatie geladen uit web.xml: " + databaseLocation);
		
		if(uniqueInstance != null){
			uniqueInstance.close();
			uniqueInstance = null;
		}
		
		
	}

	public void createDatabase() {
		try {
			Class.forName(driver).newInstance();
			
			con = DriverManager.getConnection(dbname+";create=true");
			
			
			setupDatabase();
			
			//TODO Remove next line, it's test code - Niels
			System.out.println("start nieuwe database : statement: " + dbname+";create=true");
			System.out.println("Test op bestaan: " + dbExists(dbname));
			System.out.println("Veiling test: " + con.createStatement().execute("Select * from app.veilingen"));
			
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
				
		
		
		
	}

	private void setupDatabase() throws SQLException {
		con.createStatement().execute("CREATE TABLE APP.VEILINGEN("+
				"starterID 		Int NOT NULL,"
				+"hoogBod		Int,"
				+"veilingID		Int NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 100, INCREMENT BY 1),"
				+"artikelNaam	VARCHAR(20),"
				+"imageURL		VARCHAR(250),"
				+"beschrijving	VARCHAR(500),"
				+"startDate		BIGINT,"
				+"endDate		BIGINT"
				
				+")");
		
		con.createStatement().execute("create TABLE APP.USERS		("+
						"userID		Int NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 100, INCREMENT BY 1),"+
						"saldo		INTEGER,"+
						"voornaam	VARCHAR(20),"+
						"achternaam	VARCHAR(20),"+
						"email		VARCHAR(100),"+
						"telefoon	VARCHAR(13),"+
						"wachtwoord	VARCHAR(20),"+
						"adres		VARCHAR(60)"+
						")");
		
		con.createStatement().execute("CREATE TABLE APP.BIEDINGEN	("+
						"biedingID		Int NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 100, INCREMENT BY 1),"+
						"veilingID		Int NOT NULL,"+
						"userID			Int NOT NULL,"+
						"bodHoogte		INT,"+
						"BiedingDate	BIGINT"+
						")");
		
	}
}
